/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DBLayer;

import ModelLayer.Supplier;
import java.sql.*;


import java.util.ArrayList;

/**
 *
 * @author Lie
 */
public class DbSupplier extends DBExtentions implements IFDBSupplier {

    private Connection con;
    private ArrayList<String> queryList = new ArrayList<String>();

    public DbSupplier()
    {
        con = DbConnection.getInstance().getDBcon();
    }

    public ArrayList<Supplier> getAllSupplier()
    {
        return miscWhere("");
    }

    public Supplier searchSupplierName(String name)
    {
        String wClause = "name like '%" + name + "%'";
        System.out.println("SearchSupplier " + wClause);
        return singleWhere(wClause);
    }

    public Supplier searchSupplierId(String supID)
    {
        String wClause = "personID like '%" + supID + "%'";
        System.out.println("SearchSupplier " + wClause);
        return singleWhere(wClause);
    }

    public ArrayList<Supplier> searchSuppliersByName(String name)
    {
        String wClause = "P.name like '%" + name + "%'";
        System.out.println("SearchCustomer " + wClause);
        return miscWhere(wClause);
    }

    public ArrayList<Supplier> searchSuppliersById(String cusID)
    {
        String wClause = "P.personID = '" + cusID + "'";
        System.out.println("SearchCustomer " + wClause);
        return miscWhere(wClause);
    }

    public int insertSupplier(Supplier sup)
    {
        queryList.clear();
        int nextSupplierID = GetMax.getMaxId("Select max(PersonID) from Person");
        nextSupplierID = nextSupplierID + 1;
        System.out.println("next SupplierID = " + nextSupplierID);
        int CityID = getCityID(sup.getZipCode());
        //Transaktion 1 Begins
        int rc = -1;
        String query = "INSERT INTO Person(personID, name, address, cityID, phoneNo, type, countryID)  VALUES('"
                + nextSupplierID + "', '"
                + sup.getName() + "','"
                + sup.getAddress() + "','"
                + CityID + "','"
                + sup.getPhoneNo() + "','"
                + sup.getType() + "','"
                + sup.getCountryID() + "')";

        System.out.println("insert : " + query);
        //Transaktion 1 Ends
        queryList.add(query);

        //Transaktion 2 Begins
        query = "INSERT INTO Supplier(personID, supplierID, email) VALUES("
                + nextSupplierID + ", "
                + nextSupplierID + ", '"
                + sup.getEmail() + "')";
        System.out.println("insert : " + query);
        //Transaktion 2 Ends
        queryList.add(query);
        rc = rollBack(queryList);

        return rc;


    }

    public int updateSupplier(Supplier sup)
    {
        queryList.clear();
        Supplier supObj = sup;
        int rc = -1;
        String query = "UPDATE Person SET "
                + "name ='" + supObj.getName() + "',"
                + "address ='" + supObj.getAddress() + "',"
                + "cityID ='" + getCityID(supObj.getZipCode()) + "',"
                + "phoneNo ='" + supObj.getPhoneNo() + "',"
                + "type ='" + supObj.getType() + "',"
                + "countryID ='" + supObj.getCountryID() + "' "
                + "WHERE personID = '" + supObj.getPersonID() + "'";
        System.out.println("Update query:" + query);
        queryList.add(query);



        query = "UPDATE Supplier SET "
                + "Email ='" + supObj.getEmail() + "' "
                + "WHERE personID = '" + supObj.getPersonID() + "'";
        queryList.add(query);
        rc = rollBack(queryList);
        return rc;
    }

    public int deleteSupplier(int personID)
    {
        queryList.clear();
        int rc = -1;
        //Transaktion 1 Begin
        String query = "DELETE FROM Supplier WHERE SupplierID = '"
                + personID + "'";
        System.out.println("Delete ID : " + query);
        //Transaktion 1 Ends
        queryList.add(query);
        //Transaktion 2 Begin


        query = "DELETE FROM Person WHERE personID = '"
                + personID + "'";
        System.out.println("Delete : " + query);
        //Transaktion 2 Ends
        queryList.add(query);
        rc = rollBack(queryList);

        return rc;
    }

    private ArrayList<Supplier> miscWhere(String wClause)
    {
        ResultSet results;
        ArrayList<Supplier> list = new ArrayList<Supplier>();
        String query = buildQuery(wClause);
        try
        { // read the employee from the database
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);
            while (results.next())
            {
                Supplier supObj = new Supplier();
                supObj = buildSupplier(results);
                list.add(supObj);
            }//end while
            stmt.close();
        }//slut try
        catch (Exception e)
        {
            System.out.println("Query exception - select: " + e);
            e.printStackTrace();
        }
        return list;
    }
    //Singelwhere is used when we only select one employee

    private Supplier singleWhere(String wClause)
    {
        ResultSet results;
        Supplier supObj = new Supplier();
        String query = buildQuery(wClause);
        System.out.println(query);
        try
        { // read the employee from the database
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            if (results.next())
            {
                supObj = buildSupplier(results);
            }

        }//end try
        catch (Exception e)
        {
            System.out.println("Query exception: " + e);
        }
        return supObj;
    }
    //method to build the query

    private String buildQuery(String wClause)
    {
        String query = "SELECT * FROM Person P, Country C, ZipCity Z, Supplier S";

        query = query + " WHERE p.cityID = z.cityID and "
                    + "p.countryID = c.countryID and "
                    + "p.personID = s.supplierID";
        if (wClause.length() > 0)
        {
             query += " and " + wClause;
        }

        return query;
    }
    //method to build an employee object

    private Supplier buildSupplier(ResultSet results)
    {
        Supplier supObj = new Supplier();
        try
        {
            supObj.setPersonID(results.getInt("personID"));
            supObj.setName(results.getString("name"));
            supObj.setAddress(results.getString("address"));
            supObj.setCity(results.getString("city"));
            supObj.setZipCityID(results.getInt("cityID"));
            supObj.setCountry(results.getString("countryName"));
            supObj.setCountryID(results.getInt("countryID"));
            supObj.setZipCode(getZipCode(supObj.getZipCityID()));
            supObj.setType(results.getString("type"));
            supObj.setPhoneNo(results.getString("phoneNo"));
            supObj.setCountryPhone(results.getString("countryPhone"));
            supObj.setEmail(results.getString("Email"));

        }
        catch (Exception e)
        {
            System.out.println("error in building the Supplier object");
        }
        return supObj;
    }

    public Supplier findSupplier(int ID, boolean retriveAssociation)
    {
        String wClause = "  personID = '" + ID + "'";
        return singleWhere(wClause);
    }
}
